package com.cc.dao.impl;

import com.cc.dao.AttendanceDao;
import com.cc.dao.BaseDao;
import com.cc.entity.AttendanceRecord;
import com.cc.entity.Page;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.time.LocalDate;
import java.util.*;

public class AttendanceDaoImpl extends BaseDao implements AttendanceDao {
    public boolean isClockIn(String employeeId) {
        String sql = "SELECT COUNT(1) count FROM `attendance_records` WHERE employee_id = ? " +
                "AND date = DATE_FORMAT(CURDATE(),'%Y-%m-%d')";
        ResultSet rs = executeQuery(sql, employeeId);
        if(rs != null){
            try {
                while (rs.next()){
                    return rs.getInt("count")==1;
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            } finally {
                closedAll(rs);
            }
        }
        return false;
    }

    @Override
    public void clockIn(String employeeId) {
        StringBuffer sb = new StringBuffer();
        sb.append("INSERT INTO attendance_records (employee_id,check_in_time,`status`,date) ");
        sb.append(" VALUES (?,");
        sb.append(" TIME(NOW()),");
        sb.append(" CASE WHEN TIME(NOW()) > '09:00:00' THEN '迟到' ELSE '正常' END,");
        sb.append(" DATE_FORMAT(CURDATE(),'%Y-%m-%d'))");
        executeUpdate(sb.toString(),employeeId);
    }


    @Override
    public void updateClockIn(String employeeId) {
        StringBuffer sb = new StringBuffer();
        sb.append(" UPDATE attendance_records SET check_out_time = TIME(NOW()) ");
        sb.append(" WHERE employee_id = " + employeeId);
        sb.append(" AND date = DATE_FORMAT(CURDATE(),'%Y-%m-%d')");
        executeUpdate(sb.toString());
    }

    @Override
    public Page<AttendanceRecord> getAttendanceRecord(Integer employeeId, Page<AttendanceRecord> page) {
        if (page == null) {
            return page;
        }

        // 设置总记录数
        page.setTotal(getTotal(employeeId));

        // 计算分页偏移量
        int offset = (page.getPageNum() - 1) * page.getPageSize();

        // 查询分页数据
        String sql = "SELECT id, employee_id, check_in_time, check_out_time, status, date, remark " +
                "FROM attendance_records WHERE employee_id = ? " +
                "ORDER BY date DESC, check_in_time DESC " +
                "LIMIT ? OFFSET ?";

        ResultSet rs = executeQuery(sql, employeeId, page.getPageSize(), offset);
        List<AttendanceRecord> records = new ArrayList<>();

        try {
            if (rs != null) {
                while (rs.next()) {
                    AttendanceRecord record = new AttendanceRecord();
                    record.setId(rs.getInt("id"));
                    record.setEmployeeId(rs.getInt("employee_id"));

                    // 处理时间字段
                    Timestamp checkIn = rs.getTimestamp("check_in_time");
                    record.setCheckInTime(checkIn != null ? new Date(checkIn.getTime()) : null);

                    Timestamp checkOut = rs.getTimestamp("check_out_time");
                    record.setCheckOutTime(checkOut != null ? new Date(checkOut.getTime()) : null);

                    record.setStatus(rs.getString("status"));
                    record.setDate(new Date(rs.getTimestamp("date").getTime()));
                    record.setRemark(rs.getString("remark")); // 确保添加了remark字段

                    records.add(record);
                }
            }
            page.setData(records);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closedAll(rs);
        }
        return page;
    }

    @Override
    public int getTotal(Integer employeeId) {
        String sql = "SELECT COUNT(1) FROM attendance_records WHERE employee_id = ?";
        ResultSet rs = executeQuery(sql, employeeId);

        try {
            if (rs != null && rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closedAll(rs);
        }
        return 0;
    }

    @Override
    public Map<String, Object> getAttendanceOverview(Integer employeeId) {
        LocalDate now = LocalDate.now();
        int year = now.getYear();
        int month = now.getMonthValue();

        Map<String, Object> result = new HashMap<>();

        // 统计出勤天数
        String sql1 = "SELECT COUNT(*) FROM attendance_records WHERE employee_id = ? " +
                "AND YEAR(date) = ? AND MONTH(date) = ? AND status IN ('正常', '迟到')";
        ResultSet rs1 = executeQuery(sql1, employeeId, year, month);
        try {
            if (rs1 != null && rs1.next()) {
                result.put("attendanceDays", rs1.getInt(1));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closedAll(rs1);
        }

        // 统计迟到次数
        String sql2 = "SELECT COUNT(*) FROM attendance_records WHERE employee_id = ? " +
                "AND YEAR(date) = ? AND MONTH(date) = ? AND status = '迟到'";
        ResultSet rs2 = executeQuery(sql2, employeeId, year, month);
        try {
            if (rs2 != null && rs2.next()) {
                result.put("lateCount", rs2.getInt(1));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closedAll(rs2);
        }

        // 获取最近一次迟到记录
        result.put("lastLateRecord", getLastLateRecord(employeeId));

        // 统计请假天数
        result.put("leaveDays", countLeaveDays(employeeId, year, month));

        // 统计加班时长
        result.put("overtimeHours", sumOvertimeHours(employeeId, year, month));

        // 获取年假剩余天数
        result.put("annualLeaveLeft", getAnnualLeaveLeft(employeeId));

        return result;
    }

    @Override
    public AttendanceRecord getLastLateRecord(Integer employeeId) {
        String sql = "SELECT * FROM attendance_records WHERE employee_id = ? " +
                "AND status = '迟到' ORDER BY date DESC LIMIT 1";
        ResultSet rs = executeQuery(sql, employeeId);
        try {
            if (rs != null && rs.next()) {
                AttendanceRecord record = new AttendanceRecord();
                record.setId(rs.getInt("id"));
                record.setEmployeeId(rs.getInt("employee_id"));
                record.setCheckInTime(rs.getTimestamp("check_in_time") != null ?
                        new Date(rs.getTimestamp("check_in_time").getTime()) : null);
                record.setDate(rs.getTimestamp("date") != null ?
                        new Date(rs.getTimestamp("date").getTime()) : null);
                record.setStatus(rs.getString("status"));
                return record;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closedAll(rs);
        }
        return null;
    }

    @Override
    public int countLeaveDays(Integer employeeId, int year, int month) {
        String sql = "SELECT SUM(DATEDIFF(end_date, start_date) + 1) " +
                "FROM leave_applications WHERE employee_id = ? " +
                "AND YEAR(start_date) = ? AND MONTH(start_date) = ? " +
                "AND status = '已批准'";
        ResultSet rs = executeQuery(sql, employeeId, year, month);
        try {
            if (rs != null && rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closedAll(rs);
        }
        return 0;
    }

    @Override
    public double sumOvertimeHours(Integer employeeId, int year, int month) {
        String sql = "SELECT SUM(hours) FROM overtime_applications " +
                "WHERE employee_id = ? AND YEAR(overtime_date) = ? " +
                "AND MONTH(overtime_date) = ? AND status = '已批准'";
        ResultSet rs = executeQuery(sql, employeeId, year, month);
        try {
            if (rs != null && rs.next()) {
                return rs.getDouble(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closedAll(rs);
        }
        return 0;
    }

    @Override
    public int getAnnualLeaveLeft(Integer employeeId) {
        String sql = "SELECT 15 - IFNULL(SUM(DATEDIFF(end_date, start_date) + 1), 0) " +
                "FROM leave_applications WHERE employee_id = ? " +
                "AND YEAR(start_date) = YEAR(CURDATE()) " +
                "AND status = '已批准'";
        ResultSet rs = executeQuery(sql, employeeId);
        try {
            if (rs != null && rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closedAll(rs);
        }
        return 15; // 默认15天年假
    }
}
